﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad

Public Class DocumentoAlmacenDetallePickeadorDatos

    Function obtenerCodigoGeneradoDADP(tipoDOC As String) As Integer
        Dim numero As Integer = 0
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()
            Dim query As String = "SELECT COUNT(*) AS Contar " &
                                  "FROM documento_almacen_detalle_pickeador " &
                                  "WHERE TDOC_Codigo = '" & tipoDOC & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        numero = CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) + 1

        Return numero
    End Function

    Sub registrarDocumentoAlmacenDetallePickeador(ByVal dadp As DocumentoAlmacenDetallePickeador)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Documento_Almacen_Detalle_Pickeador(CIA,SUC_Codigo,ALM_Codigo,TDOC_Codigo, " & _
                              "DCA_Codigo,DAD_Codigo,DAP_Codigo,DADP_Codigo,PICK_Codigo,DADP_Cantidad, " & _
                              "DADP_FechaAsignacion,DADP_FechaInicio,DADP_FechaFin,DADP_CantPick, " & _
                              "DADP_Asig_Act,DADP_Activo) VALUES(@cia,@suc,@alm,@tipodoc,@dcacod,@dadcod,@dapcod, " & _
                              "@dadpcod,@pickcod,@dadpcant,getDATE(),null,null,0,'0',@activo)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cia", dadp.CIA)
            .Parameters.AddWithValue("@suc", dadp.SUC)
            .Parameters.AddWithValue("@alm", dadp.ALM)
            .Parameters.AddWithValue("@tipodoc", dadp.TIPODOC)
            .Parameters.AddWithValue("@dcacod", dadp.DCACOD)
            .Parameters.AddWithValue("@dadcod", dadp.DADCOD)
            .Parameters.AddWithValue("@dapcod", dadp.DAPCOD)
            .Parameters.AddWithValue("@dadpcod", dadp.DADPCOD)
            .Parameters.AddWithValue("@pickcod", dadp.PICKCOD)
            .Parameters.AddWithValue("@dadpcant", dadp.CANTIDAD)
            .Parameters.AddWithValue("@activo", dadp.ACTIVO)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function verificarExistenciaDocumentoAlmacenDetallePickeador(ByVal codPedido As String, ByVal codPick As String, ByVal pddcod As String, tipoDOC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCA_Codigo, PICK_Codigo " &
                              "FROM Documento_Almacen_Detalle_Pickeador " &
                              "WHERE DCA_Codigo = '" & codPedido & "' AND PICK_Codigo = '" & codPick & "' " &
                              "AND DAD_Codigo = '" & pddcod & "' AND DADP_Activo <> '0' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count > 0 Then
            valor = True
        End If

        Return valor
    End Function

    Sub actualizarCantidadDocumentoAlmacenDetallePickeador(ByVal codPedido As String, ByVal codPick As String, ByVal cant As String, ByVal pddCod As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Almacen_Detalle_Pickeador " &
                              "SET DADP_Cantidad = DADP_Cantidad + @cant " &
                              "WHERE DCA_Codigo = @dcaCodigo " &
                              "AND PICK_Codigo = @pickeador " &
                              "AND DAD_Codigo = @dadcod " &
                              "AND DADP_Activo <> '0' " &
                              "AND TDOC_Codigo = @tipo"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cant", cant)
            .Parameters.AddWithValue("@dcaCodigo", codPedido)
            .Parameters.AddWithValue("@pickeador", codPick)
            .Parameters.AddWithValue("@dadcod", pddCod)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function verificarInicioPicking(codPedido As String, tipoDOC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DADP_Codigo " &
                              "FROM documento_almacen_detalle_pickeador " &
                              "WHERE DCA_Codigo = '" & codPedido & "' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "' " &
                              "AND DADP_Activo = '1' " &
                              "AND DADP_FechaInicio IS NOT NULL"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count <> 0 Then
            valor = True
        End If

        Return valor
    End Function

    Function verificarAsignacionPedido(ByVal codPedido As String, ByVal codPick As String, tipoDOC As String) As String
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DADP_Asig_Act " &
                              "FROM Documento_Almacen_Detalle_Pickeador " &
                              "WHERE DCA_Codigo = '" & codPedido & "' " &
                              "AND PICK_Codigo = '" & codPick & "' " &
                              "AND DADP_Activo = '1' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        Return ds.Tables(0).Rows(0)("DADP_Asig_Act").ToString.Trim
    End Function

    Sub cambiarAsignacionDocumentoAlmacenPickeador(ByVal codPedido As String, ByVal act As String, ByVal codPick As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Almacen_Detalle_Pickeador " &
                              "SET DADP_Asig_Act = @act " &
                              "WHERE DCA_Codigo = @dcaCodigo " &
                              "AND PICK_Codigo = @pickCodigo " &
                              "AND TDOC_Codigo = @tipo"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@act", act)
            .Parameters.AddWithValue("@dcaCodigo", codPedido)
            .Parameters.AddWithValue("@pickCodigo", codPick)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function verificarInicioPickeo(ByVal pedCodigo As String, ByVal pickCodigo As String, tipoDOC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT COUNT(*) AS contar FROM Documento_Almacen_Detalle_Pickeador " &
                              "WHERE DCA_Codigo = '" & pedCodigo & "' AND PICK_Codigo = '" & pickCodigo & "' " &
                              "AND DADP_FechaInicio IS NOT NULL AND DADP_Activo = '1' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If CInt(ds.Tables(0).Rows(0)("contar").ToString.Trim) > 0 Then
            valor = True
        End If

        Return valor
    End Function

    Function obtenerDocumentoAlmacenDetallePickeador(ByVal pedCodigo As String, ByVal codPick As String, tipoDOC As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DADP_Codigo,DAD_Codigo,DADP_Cantidad " &
                              "FROM Documento_Almacen_Detalle_Pickeador " &
                              "WHERE DCA_Codigo = '" & pedCodigo & "' " &
                              "AND PICK_Codigo = '" & codPick & "' " &
                              "AND DADP_Activo = 1 " &
                              "AND TDOC_Codigo = '" & tipoDOC & "' " &
                              "AND DADP_FechaInicio IS NULL " &
                              "AND DADP_CantPick = 0"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        Return ds
    End Function

    Sub actualizarPedidoPickeadorFechaFin(ByVal codPedido As String, ByVal codPick As String, ByVal pddCod As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Almacen_Detalle_Pickeador " &
                              "SET DADP_FechaFin = getDate() " &
                              "WHERE DCA_Codigo = @dcaCodigo " &
                              "AND DAD_Codigo = @dadCodigo AND " &
                              "PICK_Codigo = @pickCodigo " &
                              "AND TDOC_Codigo = @tipo"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@dcaCodigo", codPedido)
            .Parameters.AddWithValue("@dadCodigo", pddCod)
            .Parameters.AddWithValue("@pickCodigo", codPick)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function listarDocumentoAlmacenDetallePickeador() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dad.DCA_Codigo,u.USU_Codigo AS codPick, u.USU_Descripcion as Pickeador, " &
                              "dc.DCI_Codigo as NroPedido, " &
                              "(CASE  WHEN dc.DCI_Estado IN ('01') THEN 'Apro bado' " &
                              "WHEN dc.DCI_Estado IN ('15','16') THEN 'Asignado' " &
                              "WHEN dc.DCI_Estado IN ('06') THEN 'Picking'  END) as Estado, " &
                              "cc.CAC_Descripcion AS TipCliente, " &
                              "dc.DCI_Fecha as FRegistro, " &
                              "COUNT(dadp.DCA_Codigo) AS NroItem, " &
                              "'  ' + CAST(SUM(CASE WHEN dadp.DADP_FechaFin IS NULL  THEN 0 ELSE 1 END) * 100 / COUNT(dadp.DCA_Codigo) AS VARCHAR(10)) + ' %' AS Avance " &
                              "FROM Documento_Almacen_Detalle_Pickeador dadp " &
                              "INNER JOIN Usuario u ON u.USU_Codigo = dadp.PICK_Codigo AND u.CIA = dadp.CIA AND u.SUC_Codigo = dadp.SUC_Codigo AND u.USU_Activo = '1' " &
                              "LEFT JOIN Documento_Almacen_Pickeador dap ON dap.DCA_Codigo = dadp.DCA_Codigo  " &
                              "AND dap.PICK_Codigo = dadp.PICK_Codigo AND dap.DAP_FechaFinAsig IS NOT NULL " &
                              "AND dap.DAP_Activo = '1' AND dap.CIA = dadp.CIA AND dap.SUC = dadp.SUC_Codigo " &
                              "AND dap.ALM_Codigo = dadp.ALM_Codigo " &
                              "LEFT JOIN Documento_Almacen_Detalle dad ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo " &
                              "AND dad.TDOC_Codigo = dadp.TDOC_Codigo AND dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo " &
                              "AND dad.DAD_Activo = '1' " &
                              "LEFT JOIN  Documento_Cliente_Almacen_Detalle dcad ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC " &
                              "AND dcad.TDOC_Almacen = dad.TDOC_Codigo AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo " &
                              "INNER JOIN Documento_Cliente dc ON dc.CIA = dcad.CIA_Cliente AND dc.SUC_Codigo = dcad.SUC_Cliente " &
                              "AND dc.TDOC_Codigo = dcad.TDOC_Cliente AND dc.DCI_Codigo = dcad.DCI_Codigo " &
                              "AND dc.DCI_Estado IN ('01','06','15','16') " &
                              "INNER JOIN Cliente cli on dc.Cli_Codigo = cli.CLI_Codigo " &
                              "INNER JOIN CategoriaCliente cc ON cc.CAC_Codigo = cli.CAC_Codigo AND cc.CAC_Activo = '1' " &
                              "WHERE dadp.DADP_Activo = '1' " &
                              "GROUP BY dad.DCA_Codigo,u.USU_Codigo,u.USU_Descripcion, dc.DCI_Codigo, dc.DCI_Fecha, cc.CAC_Descripcion,dc.DCI_Estado"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerCodigosDetalleNoPickeados(cia As String, suc As String, alm As String, tdoc As String, dcaCodigo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DAD_Codigo " &
                              "FROM Documento_Almacen_Detalle_Pickeador " &
                              "WHERE CIA = '" & cia & "' AND SUC_Codigo = '" & suc & "' " &
                              "AND ALM_Codigo = '" & alm & "' AND TDOC_Codigo = '" & tdoc & "' " &
                              "AND DCA_Codigo = '" & dcaCodigo & "' AND DADP_FechaInicio IS NULL " &
                              "AND DADP_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerCantidadDetallesAsignados(cia As String, suc As String, alm As String, tdoc As String, dcaCodigo As String) As Integer
        Dim cantidad As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DAD_Codigo " &
                              "FROM Documento_Almacen_Detalle_Pickeador " &
                              "WHERE CIA = '" & cia & "' AND SUC_Codigo = '" & suc & "' " &
                              "AND ALM_Codigo = '" & alm & "' AND TDOC_Codigo = '" & tdoc & "' " &
                              "AND DCA_Codigo = '" & dcaCodigo & "' " &
                              "AND DADP_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        cantidad = ds.Tables(0).Rows.Count

        coneccion.Close()

        Return cantidad
    End Function

    Function obtenerArticulosPickeados(codPedido As String, codPickeador As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT a.ART_Codigo, a.ART_Descripcion, dadp.DADP_CantPick, um.UNM_Abreviatura " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
                              "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo " &
                              "AND dadp.PICK_Codigo = @PICK AND dadp.DADP_Activo = '1' AND dadp.DADP_FechaFin IS NOT NULL " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dad.ART_Codigo AND a.ART_Activo = '1' " &
                              "INNER JOIN UnidadMedida um " &
                              "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                              "WHERE dcad.DCI_Codigo = @PED AND dcad.DCAD_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@PICK", codPickeador)
            .Parameters.AddWithValue("@PED", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

End Class
